package com.wanmait.CarTrading.DAO.impl;

import com.mysql.cj.util.StringUtils;
import com.wanmait.CarTrading.DAO.CarDAO;
import com.wanmait.CarTrading.Util.DBHelper;
import com.wanmait.CarTrading.Util.Pager;
import com.wanmait.CarTrading.vo.Car;
import com.wanmait.CarTrading.vo.Cartype;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository("carDAO")
public class CarDAOImpl implements CarDAO {

    public List<Car>findByCarTypeId(int carTypeId){

    StringBuilder sb=new StringBuilder();
     sb.append(" SELECT car.id,car.carname,car.`enable`, ")
     .append(" car.colour,car.km,car.price,car.age,car.car_type_id, ")
     .append(" cartype.id,cartype.car_type_name,cartype.`enable` ")
     .append(" FROM car JOIN cartype on car.car_type_id=cartype.id ")
     .append(" WHERE cartype.id=? AND car.`enable`=1 AND cartype.`enable`=1 ");
        Car car=null;
        List<Car> cars=new ArrayList<Car>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql=sb.toString();

        try {
            conn= DBHelper.getDBhelper().getConn();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,carTypeId);
            rs=ps.executeQuery();

            while (rs.next()){
                car=new Car();
                Cartype cartype=new Cartype();
                cartype.setCarTypeName(rs.getString("cartype.car_type_name"));
                cartype.setId(rs.getInt("cartype.id"));
                car.setAge(rs.getString("car.age"));
                car.setCarname(rs.getString("car.carname"));
                car.setKm(rs.getString("car.km"));
                car.setCartype(cartype);
                car.setId(rs.getInt("car.id"));
                car.setPrice(rs.getString("car.price"));
                car.setColour(rs.getString("car.colour"));
                cars.add(car);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.getDBhelper().close(rs,ps,conn);
        }


        return  cars;
    }
    public int getDateCount(Integer carTypeId,String title){
        int count=0;
        StringBuilder sb=new StringBuilder();

        sb.append("  select count(*) c  FROM car ");
        sb.append(" JOIN cartype on car.car_type_id=cartype.id ");
        sb.append(" WHERE car.`enable`=1 AND  cartype.`enable`=1 ");
        if(carTypeId!=null){
            sb.append(" AND cartype.id=? ");
        }
        if(!StringUtils.isNullOrEmpty(title)){
            sb.append(" AND car.carname LIKE ? ");
        }

        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql=sb.toString();

        try {
            conn= DBHelper.getDBhelper().getConn();
            ps=conn.prepareStatement(sql);
            int index=1;
            if(carTypeId!=null){
                ps.setInt(index++,carTypeId);
            }
            if(!StringUtils.isNullOrEmpty(title)){
                ps.setString(index++,"%"+title+"%");
            }

            rs=ps.executeQuery();
           rs.next();
           count=rs.getInt("c");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.getDBhelper().close(rs,ps,conn);
        }


        return  count;

    }

    public List<Car> findAll(Integer carTypeId, String title, Pager pager)
    {
      List<Car>cars=new ArrayList<>();
      StringBuilder sb=new StringBuilder();
        sb.append("SELECT car.id, car.carname ,car.colour,car.price,car.age,car.car_type_id, ");
        sb.append("  car.km,cartype.id,cartype.car_type_name FROM car ");
        sb.append(" JOIN cartype on car.car_type_id=cartype.id ");
        sb.append("  WHERE car.`enable`=1 AND  cartype.`enable`=1 ");
      if(carTypeId!=null){
          sb.append(" AND cartype.id=? ");
      }
      if(!StringUtils.isNullOrEmpty(title)){
          sb.append(" AND car.carname LIKE ? ");
      }
        sb.append(" LIMIT ?,? ");
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql=sb.toString();
        try {
            conn= DBHelper.getDBhelper().getConn();
            ps=conn.prepareStatement(sql);
           int index=1;
            if(carTypeId!=null){
               ps.setInt(index++,carTypeId);
            }
            if(!StringUtils.isNullOrEmpty(title)){
               ps.setString(index++,"%"+title+"%");
            }
            ps.setInt(index++,pager.getOffset());
            ps.setInt(index++,pager.getPageSize());
            rs=ps.executeQuery();
           Car car=null;
            Cartype cartype=null;
            while (rs.next()) {
                car=new Car();
                cartype=new Cartype();
            cartype.setId(rs.getInt("cartype.id"));
            cartype.setCarTypeName(rs.getString("car_type_name"));
            car.setAge(rs.getString("car.age"));
            car.setCarname(rs.getString("car.carname"));
            car.setKm(rs.getString("car.km"));
            car.setCartype(cartype);
            car.setId(rs.getInt("car.id"));
            car.setPrice(rs.getString("car.price"));
            car.setColour(rs.getString("car.colour"));
            cars.add(car);
            }

        }catch (Exception e){
            e.printStackTrace();
        }


      return  cars;
    }


    @Override
    public List<Car> findAll() {
        return null;
    }

    @Override
    public Car findById(Integer id) {

        StringBuilder sb=new StringBuilder();
        sb.append(" SELECT car.id,car.carname,car.`enable`, ")
                .append(" car.colour,car.km,car.price,car.age,car.car_type_id, ")
                .append(" cartype.id,cartype.car_type_name,cartype.`enable` ")
                .append(" FROM car JOIN cartype on car.car_type_id=cartype.id ")
                .append(" WHERE car.id=? AND car.`enable`=1 AND cartype.`enable`=1 ");
        Car car=null;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql=sb.toString();

        try {
            conn= DBHelper.getDBhelper().getConn();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();

            while (rs.next()){
                car=new Car();
                Cartype cartype=new Cartype();
                cartype.setCarTypeName(rs.getString("cartype.car_type_name"));
                cartype.setId(rs.getInt("cartype.id"));
                car.setAge(rs.getString("car.age"));
                car.setCarname(rs.getString("car.carname"));
                car.setKm(rs.getString("car.km"));
                car.setCartype(cartype);
                car.setId(rs.getInt("car.id"));
                car.setPrice(rs.getString("car.price"));
                car.setColour(rs.getString("car.colour"));


            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.getDBhelper().close(rs,ps,conn);
        }
        return car;
    }

    @Override
    public List<Car> findAll(Pager pager) {
        return null;
    }

    @Override
    public int getDataCount() {
        return 0;
    }

    @Override
    public void insert(Car object) {

    }

    @Override
    public void update(Car object) {

    }

    @Override
    public void delete(Integer id) {

    }

    @Override
    public void delete(String ids) {

    }
}
